package com.qjcc.service.impl;

import com.qjcc.domain.AjaxJson;
import com.qjcc.domain.Series;
import com.qjcc.domain.SeriesInclude;
import com.qjcc.domain.p.ZtTableDataEntity;
import com.qjcc.repository.p.ZtTableDataRepository;
import com.qjcc.service.ZtTableDataService;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import javax.sql.DataSource;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Transactional
public class ZtTableDataServiceImpl implements ZtTableDataService {

    @Autowired
    private ZtTableDataRepository ztTableDataRepository;

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    protected JdbcTemplate primaryJdbc;

    private SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM");

    @Override
    public List<ZtTableDataEntity> list() {
        return ztTableDataRepository.findAll();
    }

    @Override
    public List<ZtTableDataEntity> top12() {
        return ztTableDataRepository.findTop10ByOrderByDateAsc();
    }

    @Override
    public AjaxJson whl(String queryType){
        AjaxJson j = new AjaxJson();
        Map<String, Object> m = new HashMap<>();

        List<Map<String, Object>> dateList = getDateList();
        List<Map<String, Object>> typeList = null;

        if("gy".equals(queryType)){
            typeList = getTypeGyList();

        }else if("my".equals(queryType)){
            typeList = getTypeMyList();
        }

        //横坐标
        List<String> categories = new ArrayList<>();
        for(Map<String, Object> date: dateList){
            categories.add(date.get("xDate").toString());
        }

        //纵坐标
        List<Series> series = new ArrayList<>();
        for(int i = 0; i < typeList.size(); i++){
            String typecode = (String) typeList.get(i).get("typecode");
            String typename = (String) typeList.get(i).get("typename");
            List<Double> columnData = new ArrayList<>();
            List<Double> splineData = new ArrayList<>();

            for(Map<String, Object> obj: dateList){
                Date date = (Date) obj.get("date");
                ZtTableDataEntity tableDataEntity = ztTableDataRepository.findByTypeAndDate(typecode, date);
                //维护量
                double xValue = null != tableDataEntity ? tableDataEntity.getWhl() / 1000 : 0;
                //增长率
                double zzl = null != tableDataEntity ? tableDataEntity.getZzl() : 0;

                columnData.add(xValue);
                splineData.add(zzl);
            }

            Series seriesCol = new Series();

            Map<String, String> tooltipCol = new HashMap<>();
            tooltipCol.put("valueSuffix", " K");

            seriesCol.setName(typename);
            seriesCol.setType("column");
            seriesCol.setTooltip(tooltipCol);
            seriesCol.setyAxis(0);
            seriesCol.setData(columnData);

            Series seriesSpl = new Series();

            Map<String, String> tooltipSpl = new HashMap<>();
            tooltipSpl.put("valueSuffix", "");

            seriesSpl.setName(typename);
            seriesSpl.setType("spline");
            seriesSpl.setTooltip(tooltipSpl);
            seriesSpl.setyAxis(1);
            seriesSpl.setData(splineData);

            series.add(seriesCol);
            series.add(seriesSpl);
        }

        m.put("categories", categories);
        m.put("series", series);

        j.setAttributes(m);

        return j;
    }

    @Override
    public AjaxJson cb(String queryType){
        AjaxJson j = new AjaxJson();
        Map<String, Object> m = new HashMap<>();

        List<Map<String, Object>> dateList = getDateList();
        List<Map<String, Object>> typeList = null;

        if("gy".equals(queryType)){
            typeList = getTypeGyList();

        }else if("my".equals(queryType)){
            typeList = getTypeMyList();
        }

        //横坐标
        List<String> categories = new ArrayList<>();
        for(Map<String, Object> date: dateList){
            categories.add(date.get("xDate").toString());
        }

        //纵坐标
        List<Series> series = new ArrayList<>();
        for(int i = 0; i < typeList.size(); i++){
            String typecode = (String) typeList.get(i).get("typecode");
            String typename = (String) typeList.get(i).get("typename");
            List<Double> bycbsData = new ArrayList<>();
            List<Double> bycgcbsData = new ArrayList<>();
            List<Double> bycblData = new ArrayList<>();

            for(Map<String, Object> obj: dateList){
                Date date = (Date) obj.get("date");
                ZtTableDataEntity tableDataEntity = ztTableDataRepository.findByTypeAndDate(typecode, date);
                //本月抄表数
                double bycbs = null != tableDataEntity ? tableDataEntity.getBycbs() / 1000 : 0;
                //本月成功抄表数
                double bycgcbs = null != tableDataEntity ? tableDataEntity.getBycgcbs() / 1000 : 0;
                //抄表率
                double bycbl = null != tableDataEntity ? tableDataEntity.getBycbl() * 100 : 0;

                bycbsData.add(bycbs);
                bycgcbsData.add(bycgcbs);
                bycblData.add(bycbl);
            }

            Map<String, String> tooltipLeft = new HashMap<>();
            tooltipLeft.put("valueSuffix", " K");

            Map<String, String> tooltipRight = new HashMap<>();
            tooltipRight.put("valueSuffix", " %");

            int middle = 4;
            int index = i;
            if("gy".equals(queryType)){
                index = middle;
            }

            SeriesInclude.Builder bycbsBuilder = new SeriesInclude.Builder();
            SeriesInclude bycbsSer = bycbsBuilder.name(typename + "抄表数").type("column").tooltip(tooltipLeft).yAxis(0).pointPlacement(index).data(bycbsData).build();
            series.add(bycbsSer);

            SeriesInclude.Builder bycgcbsBuilder = new SeriesInclude.Builder();
            SeriesInclude bycgcbsSer = bycgcbsBuilder.name(typename + "成功数").type("column").tooltip(tooltipLeft).yAxis(0).pointPlacement(index).data(bycgcbsData).build();
            series.add(bycgcbsSer);

            SeriesInclude.Builder bycblBuilder = new SeriesInclude.Builder();
            SeriesInclude bycblSer = bycblBuilder.name(typename + "抄表率").type("spline").tooltip(tooltipRight).yAxis(1).pointPlacement(middle).data(bycblData).build();
            series.add(bycblSer);
        }

        m.put("categories", categories);
        m.put("series", series);

        j.setAttributes(m);

        return j;
    }

    @Override
    public AjaxJson gz(String queryType){
        AjaxJson j = new AjaxJson();
        Map<String, Object> m = new HashMap<>();

        List<Map<String, Object>> dateList = getDateList();
        List<Map<String, Object>> typeList = null;

        if("gy".equals(queryType)){
            typeList = getTypeGyList();

        }else if("my".equals(queryType)){
            typeList = getTypeMyList();
        }

        //横坐标
        List<String> categories = new ArrayList<>();
        for(Map<String, Object> date: dateList){
            categories.add(date.get("xDate").toString());
        }

        //纵坐标
        List<Series> series = new ArrayList<>();
        for(int i = 0; i < typeList.size(); i++){
            String typecode = (String) typeList.get(i).get("typecode");
            String typename = (String) typeList.get(i).get("typename");
            List<Double> topData = new ArrayList<>();
            List<Double> bottomData = new ArrayList<>();
            List<Double> percentData = new ArrayList<>();

            for(Map<String, Object> obj: dateList){
                Date date = (Date) obj.get("date");
                ZtTableDataEntity tableDataEntity = ztTableDataRepository.findByTypeAndDate(typecode, date);
                //本月新增
                double top = null != tableDataEntity ? tableDataEntity.getByxz() / 1000 : 0;
                //维护量
                double bottom = null != tableDataEntity ? tableDataEntity.getWhl() / 1000 : 0;
                //故障率
                double percent = null != tableDataEntity ? tableDataEntity.getGzl() * 100 : 0;

                topData.add(top);
                bottomData.add(bottom);
                percentData.add(percent);
            }

            Map<String, String> tooltipLeft = new HashMap<>();
            tooltipLeft.put("valueSuffix", " K");

            Map<String, String> tooltipRight = new HashMap<>();
            tooltipRight.put("valueSuffix", " %");

            int middle = 4;
            int index = i;
            if("gy".equals(queryType)){
                index = middle;
            }

            SeriesInclude.Builder topBuilder = new SeriesInclude.Builder();
            SeriesInclude topSer = topBuilder.name(typename + "本月新增").type("column").tooltip(tooltipLeft).yAxis(0).pointPlacement(index).data(topData).build();
            series.add(topSer);

            SeriesInclude.Builder bottomBuilder = new SeriesInclude.Builder();
            SeriesInclude bottomSer = bottomBuilder.name(typename + "维护量").type("column").tooltip(tooltipLeft).yAxis(0).pointPlacement(index).data(bottomData).build();
            series.add(bottomSer);

            SeriesInclude.Builder percentBuilder = new SeriesInclude.Builder();
            SeriesInclude percentSer = percentBuilder.name(typename + "故障率").type("spline").tooltip(tooltipRight).yAxis(1).pointPlacement(middle).data(percentData).build();
            series.add(percentSer);
        }

        m.put("categories", categories);
        m.put("series", series);

        j.setAttributes(m);

        return j;
    }

    @Override
    public AjaxJson gzcz(String queryType){
        AjaxJson j = new AjaxJson();
        Map<String, Object> m = new HashMap<>();

        List<Map<String, Object>> dateList = getDateList();
        List<Map<String, Object>> typeList = null;

        if("gy".equals(queryType)){
            typeList = getTypeGyList();

        }else if("my".equals(queryType)){
            typeList = getTypeMyList();
        }

        //横坐标
        List<String> categories = new ArrayList<>();
        for(Map<String, Object> date: dateList){
            categories.add(date.get("xDate").toString());
        }

        //纵坐标
        List<Series> series = new ArrayList<>();
        for(int i = 0; i < typeList.size(); i++){
            String typecode = (String) typeList.get(i).get("typecode");
            String typename = (String) typeList.get(i).get("typename");
            List<Double> topData = new ArrayList<>();
            List<Double> bottomData = new ArrayList<>();
            List<Double> percentData = new ArrayList<>();

            for(Map<String, Object> obj: dateList){
                Date date = (Date) obj.get("date");
                ZtTableDataEntity tableDataEntity = ztTableDataRepository.findByTypeAndDate(typecode, date);
                //本月成功处置
                double top = null != tableDataEntity ? tableDataEntity.getBycgcz() / 1000 : 0;
                //需要处置数
                double bottom = null != tableDataEntity ? tableDataEntity.getXyczl() / 1000 : 0;
                //本月修复率
                double percent = null != tableDataEntity ? tableDataEntity.getByxfl() * 100 : 0;

                topData.add(top);
                bottomData.add(bottom);
                percentData.add(percent);
            }

            Map<String, String> tooltipLeft = new HashMap<>();
            tooltipLeft.put("valueSuffix", " K");

            Map<String, String> tooltipRight = new HashMap<>();
            tooltipRight.put("valueSuffix", " %");

            int middle = 4;
            int index = i;
            if("gy".equals(queryType)){
                index = middle;
            }

            SeriesInclude.Builder topBuilder = new SeriesInclude.Builder();
            SeriesInclude topSer = topBuilder.name(typename + "已处置").type("column").tooltip(tooltipLeft).yAxis(0).pointPlacement(index).data(topData).build();
            series.add(topSer);

            SeriesInclude.Builder bottomBuilder = new SeriesInclude.Builder();
            SeriesInclude bottomSer = bottomBuilder.name(typename + "需处置").type("column").tooltip(tooltipLeft).yAxis(0).pointPlacement(index).data(bottomData).build();
            series.add(bottomSer);

            SeriesInclude.Builder percentBuilder = new SeriesInclude.Builder();
            SeriesInclude percentSer = percentBuilder.name(typename + "处置率").type("spline").tooltip(tooltipRight).yAxis(1).pointPlacement(middle).data(percentData).build();
            series.add(percentSer);
        }

        m.put("categories", categories);
        m.put("series", series);

        j.setAttributes(m);

        return j;
    }

    /**
     * 得到X坐标日期
     * @return
     */
    private List<Map<String, Object>> getDateList(){
        String sql = "select DISTINCT d.date as date, DATE_FORMAT(d.date,'%Y%m') as xDate from zt_table_data d order by d.date DESC limit 0, 12";
        List<Map<String, Object>> list = primaryJdbc.queryForList(sql);

        Collections.reverse(list);
        return list;
    }

    /**
     * 得到表类型集合
     * @return
     */
    private List<Map<String, Object>> getTypeMyList(){
        String sql = "select tt.typecode, tt.typename from t_s_type tt left join t_s_typegroup gg on tt.typegroupid = gg.id where gg.typegroupcode = 'table_type' and tt.typecode != 'gyb'";
        List<Map<String, Object>> list = primaryJdbc.queryForList(sql);
        return list;
    }

    private List<Map<String, Object>> getTypeGyList(){
        String sql = "select tt.typecode, tt.typename from t_s_type tt left join t_s_typegroup gg on tt.typegroupid = gg.id where gg.typegroupcode = 'table_type' and tt.typecode = 'gyb'";
        List<Map<String, Object>> list = primaryJdbc.queryForList(sql);
        return list;
    }

}
